/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.bancofloripa.dao;

import br.com.bancofloripa.entity.Cliente;
import br.com.bancofloripa.entity.Cliente;
import br.com.bancofloripa.exception.MensagemExceptionDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author fabianoc
 */
public class ClienteDAO extends BaseDao {

    public int insere(Cliente cl) throws MensagemExceptionDAO {
        int id = 0;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int i = 0;
        try {
            StringBuilder sb = new StringBuilder();
            c = super.getConexaoBD();
            sb.append("INSERT INTO cliente ");
            sb.append("(nome, cpf, senha) values (?, ?, ?)");
            ps = c.prepareStatement(sb.toString(), ps.RETURN_GENERATED_KEYS);
            ps.setString(++i, cl.getNome());
            ps.setString(++i, cl.getCpf());
            ps.setString(++i, cl.getSenha());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            rs.next();
                id = rs.getInt(1);
        } catch (SQLException ex) {
            throw new MensagemExceptionDAO("Erro de sql "+ex.getMessage());
            
        } catch (NullPointerException ex){
            throw new MensagemExceptionDAO("NullPointer "+ex.getMessage());
            
        } catch (MensagemExceptionDAO ex) {
            
            ex.getMessage();
        }        
        return id;
    }

    public void atualiza(Cliente cl) throws MensagemExceptionDAO {
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        int i = 0;
        try {
            c = super.getConexaoBD();
            sb.append("UPDATE cliente SET ");
            sb.append("nome = ? , cpf = ? , senha = ? WHERE id = ?");
            ps = c.prepareStatement(sb.toString());
            ps.setString(++i, cl.getNome());
            ps.setString(++i, cl.getCpf());
            ps.setString(++i, cl.getSenha());
            ps.setInt(++i, cl.getId());        
            ps.execute();
        } catch (SQLException ex) {
            throw new MensagemExceptionDAO("Erro de sql "+ex.getMessage());
        } catch (MensagemExceptionDAO ex) {
            ex.getMessage();
        }        
    }

    public List<Cliente> getAll()  {
       List<Cliente> lista = new ArrayList<Cliente>();
       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("select * from cliente");
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                Cliente cli = new Cliente(rs.getString("nome"), rs.getString("cpf"), rs.getString("senha"), rs.getInt("id"));
                //System.out.println("cliente DAO "+cli.getNome());
                lista.add( cli );
            }
        } catch (MensagemExceptionDAO ex) {
            Logger.getLogger(ClienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
         finally {
            this.closeConnection(ps, c);
       }
       return lista;

    }

    public Cliente getByFilter(int id) {
       Cliente cl = null;
       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       int i = 0;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("SELECT * FROM cliente"
                    + " where id = ? "
                    );
            ps.setInt(++i, id);
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                
                cl = new Cliente(rs.getString("nome"), rs.getString("cpf"), rs.getString("senha"), rs.getInt("id") );
            }
        } catch (MensagemExceptionDAO ex) {
            Logger.getLogger(ClienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
         finally {
            this.closeConnection(ps, c);
       }
       return cl;
    }

    public List<Cliente> getByDesc(String nome) throws MensagemExceptionDAO {
       nome = "%"+ nome + "%";
       List<Cliente> lista = new ArrayList<Cliente>();
       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("select * from cliente where nome like ? or cpf like ? ");
            ps.setString(1, nome);
            ps.setString(2, nome);
            ps.setString(3, nome);
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                Cliente cli = new Cliente(rs.getString("nome"), rs.getString("cpf"), rs.getString("senha"), rs.getInt("id"));
                
                lista.add( cli );
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
         finally {
            this.closeConnection(ps, c);
       }
       return lista;
    }
    

    
    public static void main(String[] args) {
        try {
            
            ClienteDAO cd = new ClienteDAO();
            
            Cliente cc = new Cliente("carlos", "75293754", "senha");
            System.out.println("ID: " + cd.insere(cc));
            //System.out.println(cc);
            
            
            List<Cliente> lista = new ClienteDAO().getAll();
            
            for ( int i = 0; i < lista.size(); i++ ){
                System.out.println(lista.get(i).getNome());
            }

            
        } catch (MensagemExceptionDAO ex) {
            ex.printStackTrace();
            //System.out.println(ex);
        }
    }
    
    
}
